import json
import sqlite3
from datetime import datetime

def parse_date(date_str):
    """解析日期字符串，返回datetime对象用于排序"""
    try:
        return datetime.strptime(date_str, '%Y-%m-%d')
    except:
        return datetime.min

def import_title_links():
    # 读取 title_link.json
    try:
        with open(r'E:\新闻聚合与摘要平台\high-quality-info-aggregation-main\high-quality-info-aggregation-main\bind\init_bind\title_link.json', 'r', encoding='utf-8') as f:
            content = f.read().strip()
            if not content:  # 如果文件为空
                print("文件为空，没有数据可导入")
                return
            data = json.loads(content)
        print(f"读取到 {len(data)} 条数据")
    except FileNotFoundError:
        print("未找到文件 bind/init_bind/title_link.json")
        return
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return
# 在数据库中添加用户收藏表
def create_user_favorites_table():
    c.execute('''
        CREATE TABLE IF NOT EXISTS user_favorites (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            article_id INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES title_link(id)
        )
    ''')# 添加用户阅读历史表
def create_reading_history_table():
    c.execute('''
        CREATE TABLE IF NOT EXISTS reading_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            article_id INTEGER NOT NULL,
            read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES title_link(id)
        )
    ''')# 添加文章评分和评论功能
def create_user_interactions_table():
    c.execute('''
        CREATE TABLE IF NOT EXISTS article_ratings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            article_id INTEGER NOT NULL,
            rating INTEGER CHECK(rating >= 1 AND rating <= 5),
            comment TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES title_link(id)
        )
    ''')# 添加用户搜索关键词记录
def create_search_history_table():
    c.execute('''
        CREATE TABLE IF NOT EXISTS search_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            search_query TEXT NOT NULL,
            searched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')# 添加文章标签系统
def create_tags_system():
    # 标签表
    c.execute('''
        CREATE TABLE IF NOT EXISTS tags (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL
        )
    ''')
    
    # 文章-标签关联表
    c.execute('''
        CREATE TABLE IF NOT EXISTS article_tags (
            article_id INTEGER,
            tag_id INTEGER,
            FOREIGN KEY (article_id) REFERENCES title_link(id),
            FOREIGN KEY (tag_id) REFERENCES tags(id),
            PRIMARY KEY (article_id, tag_id)
        )
    ''')# 添加用户偏好设置表
def create_user_preferences_table():
    c.execute('''
        CREATE TABLE IF NOT EXISTS user_preferences (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT UNIQUE NOT NULL,
            font_size TEXT DEFAULT 'medium',
            theme TEXT DEFAULT 'light',
            notification_enabled BOOLEAN DEFAULT 1,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')def interactive_import_options():
    """提供交互式导入选项"""
    print("请选择导入选项:")
    print("1. 全量导入 (删除旧数据)")
    print("2. 增量导入 (保留旧数据)")
    print("3. 更新已有数据")
    
    choice = input("请输入选项 (1-3): ")
    return choice

def import_with_user_tracking():
    """增强导入功能，支持用户交互数据"""
    # 创建用户交互相关的表
    create_user_favorites_table()
    create_reading_history_table()
    create_user_interactions_table()
    create_search_history_table()
    create_tags_system()
    create_user_preferences_table()
    
    print("用户交互功能表创建完成")def create_analytics_tables():
    """创建数据分析表"""
    # 文章浏览统计
    c.execute('''
        CREATE TABLE IF NOT EXISTS article_views (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            article_id INTEGER NOT NULL,
            view_count INTEGER DEFAULT 0,
            last_viewed TIMESTAMP,
            FOREIGN KEY (article_id) REFERENCES title_link(id)
        )
    ''')
    
    # 用户活跃度统计
    c.execute('''
        CREATE TABLE IF NOT EXISTS user_activity (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id TEXT NOT NULL,
            activity_type TEXT NOT NULL,  # 'view', 'favorite', 'comment', 'share'
            article_id INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    # 按时间从旧到新排序
    sorted_data = sorted(data.items(), key=lambda x: parse_date(x[1][1]), reverse=False)
    print("数据已按时间从旧到新排序")

    # 创建数据库
    db_path = r"E:\新闻聚合与摘要平台\high-quality-info-aggregation-main\high-quality-info-aggregation-main\database\title_link.db"
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('DROP TABLE IF EXISTS title_link')
    c.execute('''
        CREATE TABLE title_link (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT,
            link TEXT,
            date TEXT,
            source TEXT,
            content TEXT
        )
    ''')

    # 插入数据
    inserted_count = 0
    
    for title, info in sorted_data:
        # info = [link, date, source]
        link = info[0] if len(info) > 0 else ''
        date = info[1] if len(info) > 1 else ''
        source = info[2] if len(info) > 2 else ''
        
        try:
            c.execute('INSERT INTO title_link (title, link, date, source, content) VALUES (?, ?, ?, ?, ?)', 
                     (title, link, date, source, ''))
            inserted_count += 1
            print(f"插入: {title} ({date})")
        except Exception as e:
            print(f"插入失败 {title}: {e}")

    # 提交更改
    conn.commit()
    conn.close()
    
    print(f"导入完成！")
    print(f"成功导入: {inserted_count} 条记录")
    
    # 验证结果
    db_path = r"E:\新闻聚合与摘要平台\high-quality-info-aggregation-main\high-quality-info-aggregation-main\database\title_link.db"
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('SELECT COUNT(*) FROM title_link')
    total_count = c.fetchone()[0]
    conn.close()
    print(f"数据库总记录数: {total_count} 条")

if __name__ == "__main__":
    import_title_links()